﻿using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tian.OperationFile.excel
{
    public static class EpplusUtil
    {
        public static void DataTableToExcel(DataTable table, string sheetname, string filepath)
        {
            FileInfo newFile = new FileInfo(filepath);
            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetname);//创建worksheet
            //先设置列名称
            for (int i = 0; i < table.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1].Value = table.Columns[i].ColumnName;
            }
            //数据样式设置
            ExcelRange rangeone = worksheet.Cells[1, 1, 1, table.Columns.Count];
            //对齐方式
            rangeone.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            //边框
            rangeone.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            rangeone.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            rangeone.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            rangeone.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            //加粗
            rangeone.Style.Font.Bold = true;
            //然后设置excel数据
            for (int i = 0; i < table.Rows.Count; i++)
            {
                //遍历列值，及填充excel
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1].Value = table.Rows[i][table.Columns[j].ToString()].ToString();
                }

            }
            //数据样式设置
            ExcelRange rangetwo = worksheet.Cells[2, 1, table.Rows.Count + 1, table.Columns.Count];
            //对齐方式
            rangetwo.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            //边框
            rangetwo.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            rangetwo.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            rangetwo.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            rangetwo.Style.Border.Right.Style = ExcelBorderStyle.Thin;

            package.SaveAs(newFile);
            worksheet.Dispose();
            package.Dispose();
        }

        public static DataTable ExcelToDataTable(string sheetname, string filepath)
        {
            try
            {
                FileInfo existingFile = new FileInfo(filepath);
                ExcelPackage package = new ExcelPackage(existingFile);
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                ExcelWorksheet worksheet = package.Workbook.Worksheets["mysheet"];//选定 指定页
                                                                                  //获取worksheet的行数
                int rows = worksheet.Dimension.End.Row;
                //获取worksheet的列数
                int cols = worksheet.Dimension.End.Column;
                DataTable table = new DataTable(worksheet.Name);
                DataRow dr = null;
                for (int i = 1; i <= rows; i++)
                {
                    if (i > 1)
                    {
                        dr = table.Rows.Add();
                    }
                    for (int j = 1; j <= cols; j++)
                    {
                        //默认将第一行设置为datatable的标题
                        if (i == 1)
                        {
                            table.Columns.Add(worksheet.Cells[i, j].Value.ToString());
                        }

                        //剩下的写入datatable
                        else
                        {
                            if (worksheet.Cells[i, j].Value == null)
                            {
                                dr[j - 1] = "";
                            }
                            else
                            {
                                dr[j - 1] = worksheet.Cells[i, j].Value.ToString();
                            }

                        }

                    }
                }
                return table;
            }
            catch (Exception ex)
            {
                string lujing = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "log.txt");
                File.WriteAllText(lujing, ex.ToString());
                return new DataTable();
            }


        }
    }
}
